﻿using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;
using System.Reflection;
//using Agos.Utils;
using Logging;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Windows.Forms;

namespace ENICHIUSURA.Utils
{
    class ExcelUtils
    {
        [DllImport("user32.dll")]
        static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);
       
        /// <summary>
        /// Export dataTable to excel file
        /// </summary>
        public static string Export(string path, DataTable dt, string fileName)
        {
            string output = "OK";
            Excel.ApplicationClass excel = null;
            try
            {
                //create a new instance of excel file 
                excel = new Excel.ApplicationClass();
                excel.Application.Workbooks.Add(true);

                //first let's extract the columns and create a little header
                //save the excel file
                Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
                worksheet.Activate();
                worksheet.Name = "input-output";

                int horizontal = 1;
                int vertical = 1;
                //foreach (DataColumn dc in dt.Columns)
                //{
                //    excel.Cells[vertical, horizontal] = dc.ColumnName;
                //    horizontal++;
                //}
                ////go to next row
                //vertical++;

                foreach (DataRow row in dt.Rows)
                {
                    horizontal = 1;
                    foreach (DataColumn column in dt.Columns)
                    {
                            ((Excel.Range)excel.Cells[vertical, horizontal]).NumberFormat = "@";
                        
                        excel.Cells[vertical, horizontal] = row[column];
                        horizontal++;
                    }
                    vertical++;
                    if (vertical == 500) break;
                }
                int x = 0;
                DirectoryInfo dr = new DirectoryInfo(path);
                dr.Create();
                FileInfo fi = new FileInfo(path + fileName);
                if (fi.Exists == true)
                    fi.Delete();
                worksheet.SaveAs(path + fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                excel.Workbooks.Close();
                excel.Quit();

            }
            catch (Exception ex)
            {
                output = ex.Message;
            }
            finally
            {
                TryToKillXlApp(excel);
            }
            return output;
        }
        
        /// <summary>
        /// Exports an entire DataTable to excel file by using the range property.
        /// </summary>
        public static string ExportDtToExcel(string path, DataTable dt, string fileName)
        {
            string output = "OK";
            string file = Path.Combine(path, fileName);
            Excel.ApplicationClass excel = null;
            try
            {
                excel = new Excel.ApplicationClass();
                excel.Visible = false;
                excel.DisplayAlerts = false;
                excel.Application.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
                worksheet.Activate();
                worksheet.Name = "input-output";

                // Create Array to hold the data of DataTable
                object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];

                //Fill DataTable in Array
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow drr = dt.Rows[r];
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        arr[r, c] = drr[c];
                    }
                }
               Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
               Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1 + dt.Rows.Count - 1, dt.Columns.Count];
               Excel.Range range = worksheet.get_Range(c1, c2); 

                //Fill Array in Excel
                range.NumberFormat = "@";
                range.Value2 = arr;  //much faster to export by using a range than by using individual cells
                range.Columns.AutoFit();
                DirectoryInfo dr = new DirectoryInfo(path);
                if (!dr.Exists)
                {
                    dr.Create();
                }
                FileInfo fi = new FileInfo(file);
                if (fi.Exists == true)
                    fi.Delete();
                
                worksheet.SaveAs(file, Excel.XlFileFormat.xlExcel7
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                

                excel.Workbooks.Close();
                excel.Quit();
            }
            catch (Exception ex)
            {
                Logger.LogInfo(ex.ToString());
                output = "notOk";
            }
            finally
            {
                TryToKillXlApp(excel);
            }
            
            return output;
        }
       
        /// <summary>
        /// Reads the excel file by using the range property.Much faster than by reading individual cells.
        /// </summary>
        /// <param name="path"></param>
        /// <param name="filename"></param>
        /// <returns>object[,] with the excel cells</returns>
        public static object[,] ReadExcel(string path, string filename)
        {
            string filePath = path;
            string fileName = filename;
            string file = Path.Combine(path, filename); 
            object[,] values = null;
            Excel.Application app = null ;
            Excel.Workbook workbook = null ;
            Excel.Worksheet sheet = null ;
            Excel.Range range = null; 
            try
            {
                app = new Excel.Application();
                workbook = app.Workbooks.Open(file,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Excel.XlPlatform.xlWindows,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value);

                sheet = (Excel.Worksheet)workbook.Worksheets[1];
                
                int iTotalColumns = sheet.UsedRange.Columns.Count;
                int iTotalRows = sheet.UsedRange.Rows.Count;

                //These two lines do the magic.
                sheet.Columns.ClearFormats();
                sheet.Rows.ClearFormats();

                iTotalColumns = sheet.UsedRange.Columns.Count;
                iTotalRows = sheet.UsedRange.Rows.Count;
                Excel.Range rng = sheet.Cells[iTotalRows, iTotalColumns] as Excel.Range;
                string down = rng.get_AddressLocal(false, false, Excel.XlReferenceStyle.xlA1,
        Type.Missing, Type.Missing); ;
                range = sheet.get_Range("A1", down); //entire range from A1 to downAddress
                values = (object[,])range.Value2; //default value for empty cells will be null !
            }

            catch (Exception exp)
            {
                Logger.LogInfo(exp.ToString());
            }
            finally
            {
                TryToKillXlApp(app);
            }
            return values;
        }
        
        /// <summary>
        /// Get a DataTable representation of the specified excel file.
        /// </summary>
        public static DataTable GetExcelDT(string path, string fileName)
        {
            return ArrayToDT(ReadExcel(path, fileName));
        }
       
        //Convert the 2d array to DataTable; DbNull.Value will be set for non-values
        // the 2d array starting index is 1 for both dimensions
        public static DataTable ArrayToDT(object [,] input)
        {
            DataTable _myDataTable = new DataTable();
            for (int i = 1; i <= input.GetLength(1); i++) //add the column names to DataTable
            {
                if (input[1,i] != null )
                _myDataTable.Columns.Add(input[1,i].ToString());
            }

            for (int j = 0; j < input.GetLength(0); j++) //linia input.getLength(0)
            {
                DataRow row = _myDataTable.NewRow(); //default value for DataTable item is not null but DbNull
                for (int i = 0; i < _myDataTable.Columns.Count; i++) //coloana input.getLegth(1)
                {
                    // by default interop excel reads dates as doubles
                    if ( j > 0 &&
                        ( i == 4 ) //the column number of date
                        )
                    {
                        try
                        {
                            row[i] = DateTime.FromOADate((double)(input[j + 1, i + 1])).ToShortDateString();//covert double to DateTime
                        }
                        catch (NullReferenceException e)
                        {
                            Logger.LogInfo("Null Reference Exception at reading input file cell: " + j + "," + i);
                        }
                        catch (InvalidCastException e)
                        {
                            Logger.LogInfo(e.Message.ToString() + "cell: " + j + "," + i);
                        }
                        catch (Exception e)
                        {
                            Logger.LogInfo(e.ToString());
                        }
                    }
                    else
                        try
                        {
                            row[i] = input[j + 1, i + 1]; 
                        }
                        catch (Exception e)
                        {
                            Logger.LogInfo(e.Message.ToString() + "cell: " + j+ "," + i);
                        }
                }
                _myDataTable.Rows.Add(row);
            }
            return _myDataTable;
        }

        /// <summary>
        /// Kills the excel process identified by Hwnd
        /// </summary>
        static void TryToKillXlApp(Excel.Application xlApp)
        {
            if (xlApp != null)
            {
                int excelProcessId = 0;
                GetWindowThreadProcessId(xlApp.Hwnd, out excelProcessId);
                Process p = Process.GetProcessById(excelProcessId);
                p.Kill();
                xlApp = null;
            }
        }
    }
}
